/*
 * 文件名:BaseDao.java
 * 创建时间:2010-03-10
 * 版本:1.0
 * 版权所有:杨涛
 */
package com.yangtao.framework.hibernate;

import com.yangtao.framework.common.Pagination;
import com.yangtao.framework.util.ReflectionUtils;
import com.yangtao.framework.web.query.Operator;
import com.yangtao.framework.web.query.QueryCondition;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.*;
import org.hibernate.criterion.Example.PropertySelector;
import org.hibernate.envers.tools.query.Parameters;
import org.hibernate.envers.tools.query.QueryBuilder;
import org.hibernate.impl.CriteriaImpl;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.proxy.HibernateProxyHelper;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.type.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 基于hibernate的完成数据库操作的基本dao，可以带属性选择器
 *
 * @param <T>
 * @author 杨涛
 */
@Repository
public abstract class BaseDao<T> extends HibernateDaoSupport {
	/**
	 * 实体类型
	 */
	protected Class<T> entityClass;
	/**
	 * 非空的属性选择器
	 */
	protected final static PropertySelector NOT_EMPTY = new NotEmptyPropertySelector();

	@Autowired
	public final void setSessionFactory2(SessionFactory sessionFactory) {
		super.setSessionFactory(sessionFactory);
	}

	@SuppressWarnings("unchecked")
	public BaseDao() {
		super();
		entityClass = (Class<T>) ((ParameterizedType) getClass()
				.getGenericSuperclass()).getActualTypeArguments()[0];
	}

	/**
	 * 根据主键删除
	 *
	 * @param id 主键
	 */
	public final void delete(final String id) {
		delete(load(id));
	}

	/**
	 * 条件查询 (可更新，结果集仍处于持久态)
	 *
	 * @param criterions
	 * @return
	 */
	public final List<T> find(final Criterion... criterions) {
		Criteria criteria = getSession().createCriteria(entityClass);
		for (Criterion criterion : criterions) {
			criteria.add(criterion);
		}
		return criteria.list();
	}

	/**
	 * 根据hql语句和分页参数，查询，返回查询结果
	 *
	 * @param page   分页参数
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return 查询结果
	 */
	public Pagination findPage(final Pagination page, final String hql,
	                           Object... values) {
		return findPage(null, page, hql, values);
	}

	/**
	 * 根据指定的hql语句按照分页参数查询
	 *
	 * @param selector 属性选择器，指定哪些延迟加载的属性要被加载出来
	 * @param page     分页参数
	 * @param hql      查询语句
	 * @param values   查询参数
	 * @return 分页结果集
	 */
	@SuppressWarnings("unchecked")
	public final Pagination findPage(final Selector selector,
	                                 final Pagination page, final String hql, Object... values) {
		if (page.isAutoCount()) {
			page.setNumRows(countHqlResult(hql, values));
		}
		Query query = createQuery(hql, values);
		setPageParameter(query, page);
		List<T> poList = query.list();
		if (poList != null) {
			List voList = new ArrayList();
			for (Object po : poList) {
				voList.add(toVO(po, selector));
			}
			page.setItems(voList);
		}
		return page;
	}

	/**
	 * 根据指定的hql语句按照分页参数查询
	 *
	 * @param page   分页参数
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return 分页结果集
	 */
	public final Pagination findPage(final Pagination page, final String hql,
	                                 Map<String, Object> values) {
		return findPage(null, page, hql, values);
	}

	/**
	 * 根据指定的hql语句按照分页参数查询
	 *
	 * @param selector 属性选择器，指定哪些延迟加载的属性要被加载出来
	 * @param page     分页参数
	 * @param hql      查询语句
	 * @param values   查询参数
	 * @return 分页结果集
	 */
	public final Pagination findPage(final Selector selector,
	                                 final Pagination page, final String hql, Map<String, Object> values) {
		Assert.notNull(page, "分页对象为空!");
		// 自动统计总记录数
		if (page.isAutoCount()) {
			page.setNumRows(countHqlResult(hql, values));
		}
		Query query = createQuery(hql, values);
		setPageParameter(query, page);
		List<T> poList = query.list();
		if (poList != null) {
			List voList = new ArrayList();
			for (Object po : poList) {
				voList.add(toVO(po, selector));
			}
			page.setItems(voList);
		}
		return page;
	}

	/**
	 * 根据Criterion约束条件查询分页结果
	 *
	 * @param page     分页参数
	 * @param criteria 约束条件
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public final Pagination findPage(final Pagination page,
	                                 final Criteria criteria) {
		return findPage(null, page, criteria);
	}

	/**
	 * 根据Criterion约束条件查询分页结果
	 *
	 * @param selector 属性选择器，指定哪些延迟加载的属性要被加载出来
	 * @param page     分页参数
	 * @param criteria 约束条件
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public final Pagination findPage(final Selector selector,
	                                 final Pagination page, final Criteria criteria) {
		Assert.notNull(page, "分页对象为空!");
		// 自动统计总记录数
		if (page.isAutoCount()) {
			page.setNumRows(countCriteriaResult(criteria));
		}
		// 设置分页参数
		setPageParameter(criteria, page);
		List<T> poList = criteria.list();
		if (poList != null) {
			List voList = new ArrayList();
			for (Object po : poList) {
				voList.add(toVO(po, selector));
			}
			page.setItems(voList);
		}
		return page;
	}

	/**
	 * 查询所有数据
	 *
	 * @return
	 */
	public final List<T> findAll() {
		String hql = "from " + entityClass.getName();
		return find(hql);
	}

	/**
	 * 设置分页参数到Query对象,辅助函数.
	 *
	 * @param q    查询
	 * @param page 分页参数
	 */
	protected void setPageParameter(final Query q, final Pagination page) {
		q.setFirstResult(page.getStart());
		q.setMaxResults(page.getCount());
	}

	/**
	 * 设置分页参数到Query对象,辅助函数
	 *
	 * @param c    查询条件
	 * @param page 分页对象
	 */
	protected void setPageParameter(final Criteria c, final Pagination page) {
		c.setFirstResult(page.getStart());
		c.setMaxResults(page.getCount());
		// TODO 增加其他的参数
	}

	/**
	 * 执行count查询获得本次Hql查询所能获得的对象总数.
	 * <p/>
	 * 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
	 *
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return 结果数目
	 */
	protected Long countHqlResult(final String hql, final Object... values) {
		String fromHql = hql;
		fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
		fromHql = StringUtils.substringBefore(fromHql, "order by");

		String countHql = "select count(*) " + fromHql;

		try {
			Long count = findUnique(countHql, values);
			return count;
		} catch (Exception e) {
			throw new RuntimeException("hql can't be auto count, hql is:"
					+ countHql, e);
		}
	}

	/**
	 * 执行count查询获得本次Hql查询所能获得的对象总数.
	 * <p/>
	 * 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
	 *
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return 结果数目
	 */
	protected Long countHqlResult(final String hql,
	                              final Map<String, Object> values) {
		String fromHql = hql;
		// select子句与order by子句会影响count查询,进行简单的排除.
		fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
		fromHql = StringUtils.substringBefore(fromHql, "order by");

		String countHql = "select count(*) " + fromHql;

		try {
			Long count = findUnique(countHql, values);
			return count;
		} catch (Exception e) {
			throw new RuntimeException("hql can't be auto count, hql is:"
					+ countHql, e);
		}
	}

	/**
	 * 执行count查询获得本次Criteria查询所能获得的对象总数.
	 *
	 * @param c 查询条件
	 * @return
	 */
	@SuppressWarnings("unchecked")
	protected Long countCriteriaResult(final Criteria c) {
		CriteriaImpl impl = (CriteriaImpl) c;

		// 先把Projection、ResultTransformer、OrderBy取出来,清空三者后再执行Count操作
		Projection projection = impl.getProjection();
		ResultTransformer transformer = impl.getResultTransformer();

		List<CriteriaImpl.OrderEntry> orderEntries = null;
		try {
			orderEntries = (List) ReflectionUtils.getFieldValue(impl,
					"orderEntries");
			ReflectionUtils
					.setFieldValue(impl, "orderEntries", new ArrayList());
		} catch (Exception e) {
		}

		// 执行Count查询
		Long totalCount = (Long) c.setProjection(Projections.rowCount())
				.uniqueResult();

		// 将之前的Projection,ResultTransformer和OrderBy条件重新设回去
		c.setProjection(projection);

		if (projection == null) {
			c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		}
		if (transformer != null) {
			c.setResultTransformer(transformer);
		}
		try {
			ReflectionUtils.setFieldValue(impl, "orderEntries", orderEntries);
		} catch (Exception e) {
		}

		return totalCount;
	}

	/**
	 * 创建Criteria对象
	 *
	 * @param criterions
	 * @return
	 */
	public final Criteria createCriteria(final Criterion... criterions) {
		Criteria criteria = getSession().createCriteria(entityClass);
		for (Criterion c : criterions) {
			criteria.add(c);
		}
		return criteria;
	}

	/**
	 * 根据主键、属性字段选择器查找(仅用于查询，结果集已经处于脱管状态)
	 *
	 * @param id 主键
	 * @return
	 */
	public final T loadForReview(final Serializable id) {
		return loadForReview(id, null);
	}

	/**
	 * 根据主键、属性字段选择器查找(仅用于查询，结果集已经处于脱管状态)
	 *
	 * @param id       主键
	 * @param selector 属性选择器
	 * @return
	 */
	public final T loadForReview(final Serializable id, final Selector selector) {
		T persistObj = (T) getHibernateTemplate().get(entityClass, id);
		if (persistObj == null) {
			return null;
		}
		return toVO(persistObj, selector);
	}

	/**
	 * 根据主键查询(用于将对象持久化)
	 *
	 * @param id 主键
	 * @return 持久化对象
	 */
	public final T load(final Serializable id) {
		return getHibernateTemplate().get(entityClass, id);
	}

	/**
	 * 根据主键查找，并且锁定当前记录(用于将对象持久化)
	 *
	 * @param id 主键
	 * @return 返回持久化状态的对象
	 */
	public final T loadForUpdate(final Serializable id) {
		return getHibernateTemplate().get(entityClass, id, LockMode.READ);
	}

	/**
	 * 根据查询对象查询
	 *
	 * @param model 查询对象
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<T> findByExample(final Object model, Object... parameters) {
		Example example = Example.create(model);
		example.setPropertySelector(NOT_EMPTY);
		example.enableLike(MatchMode.ANYWHERE);
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(example);
		addCustomCriterion(criteria, parameters);
		return criteria.list();
	}

	/**
	 * 根据查询对象查询
	 *
	 * @param selector 属性选择器，指定哪些延迟加载的属性要被加载出来
	 * @param model    查询对象
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<T> findByExample(final Selector selector, final Object model, Object... parameters) {
		Example example = Example.create(model);
		example.setPropertySelector(NOT_EMPTY);
		example.enableLike(MatchMode.ANYWHERE);
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(example);
		addCustomCriterion(criteria, parameters);
		List<T> poList = criteria.list();
		if (poList == null || poList.size() == 0)
			return null;
		List<T> voList = new ArrayList<T>(poList.size());
		for (T po : poList) {
			voList.add(toVO(po, selector));
		}
		return voList;
	}

	/**
	 * 根据查询对象查询并且分页
	 *
	 * @param page  分页对象
	 * @param model 查询对象
	 * @return 分页结果
	 */
	public Pagination findPageByExample(final Pagination page, final T model, Object... parameters) {
		return findPageByExample(null, page, model, parameters);
	}

	/**
	 * 根据查询对象查询并且分页
	 *
	 * @param selector 属性选择器，指定哪些延迟加载的属性要被加载出来
	 * @param page     分页对象
	 * @param model    查询对象
	 * @return 分页结果
	 */
	public Pagination findPageByExample(final Selector selector,
	                                    final Pagination page, final T model, Object... parameters) {
		Example example = Example.create(model);
		example.setPropertySelector(NOT_EMPTY);
		example.enableLike(MatchMode.ANYWHERE);
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(example);
		addCustomCriterion(criteria, parameters);
		return findPage(selector, page, criteria);
	}

	/**
	 * 根据属性查找对象，默认会按照相等的方式匹配
	 *
	 * @param properyName 属性名称
	 * @param value       属性值
	 * @return 对象list
	 */
	public List<T> findByProperty(final String properyName, final Object value) {
		return findByProperty(properyName, Operator.EQ, value);
	}

	/**
	 * 根据属性查找对象
	 *
	 * @param properyName 属性名称
	 * @param value       属性值
	 * @param operator    操作符
	 * @return 对象list
	 */
	public List<T> findByProperty(String properyName,
	                              Operator operator, Object value) {
		StringBuilder sb = new StringBuilder();
		sb.append("from ");
		sb.append(entityClass.getName());
		sb.append(" as model where model.");
		sb.append(properyName);
		// 根据操作符选择不同的匹配条件
		switch (operator) {
			// 大于
			case GT:
				sb.append("> ?");
				break;
			case LT:
				sb.append("< ?");
				break;
			case LIKE:
				sb.append("like %?%");
				break;
			case LE:
				sb.append("<= ?");
				break;
			case GE:
				sb.append(">= ?");
				break;
			case NE:
				sb.append("!= ?");
				break;
			default:
				sb.append("= ?");
				break;
		}
		return find(sb.toString(), value);
	}

	/**
	 * 根据查询条件类自动分页查询
	 *
	 * @param page       分页对象
	 * @param conditions 查询条件
	 * @return
	 */
	public final Pagination findPageByCondition(Pagination page, List<QueryCondition> conditions, Object... params) {
		return findPageByCondition(null, page, conditions, params);
	}

	/**
	 * 根据查询条件类自动分页查询
	 *
	 * @param page       分页对象
	 * @param conditions 查询条件
	 * @return
	 */
	public final Pagination findPageByCondition(Selector selector, Pagination page, List<QueryCondition> conditions, Object... params) {
		Criteria criteria = buildCritria(conditions);
		addCustomCriterion(criteria, params);
//		QueryBuilder queryBuilder = buildQuery(conditions);
//		StringBuilder sb = new StringBuilder();
//		Map<String, Object> param = new HashMap<String, Object>();
//		queryBuilder.build(sb, param);
//		findPage(selector, page, sb.toString(), param);
		if (criteria != null) {
			return findPage(selector, page, criteria);
		}
		return page;
	}

	/**
	 * TODO
	 * 根据条件自动查询
	 *
	 * @param conditions 查询条件
	 * @return
	 */
	public final List<T> findByCondition(final List<QueryCondition> conditions) {
		Criteria criteria = buildCritria(conditions);
		if (criteria != null) {
			return criteria.list();
		}
		return null;
	}

	/**
	 * TODO
	 * 根据条件自动查询
	 *
	 * @param conditions 查询条件
	 * @return
	 */
	public final List<T> findByCondition(final Selector selector, final List<QueryCondition> conditions) {
		Criteria criteria = buildCritria(conditions);
		if (criteria != null) {
			//增加自定义的其他查询条件
			addCustomCriterion(criteria);
			return find(selector, criteria);
		}
		return null;
	}

	/**
	 * 根据查询条件组件 QueryBuilder
	 * QueryBuilder 的用法请参考hibernate源代码
	 *
	 * @param conditions 查询条件
	 * @return
	 */
	protected Criteria buildCritria(final List<QueryCondition> conditions) {
		if (conditions != null && conditions.size() > 0) {
			Criteria criteria = getSession().createCriteria(entityClass);
			ClassMetadata metadata = getSessionFactory().getClassMetadata(entityClass);
			for (QueryCondition qc : conditions) {
				if (qc == null) continue;
				Type type = metadata.getPropertyType(qc.getPropertyName());
				switch (qc.getOperator()) {
					case LIKE:
						if (StringUtils.isNotEmpty(qc.getValue())) {

							switch (qc.getMatchMode()) {
								case EXACT:
									criteria.add(Restrictions.like(qc.getPropertyName(), qc.getValue(), MatchMode.EXACT));
									break;
								case AFTER:
									criteria.add(Restrictions.like(qc.getPropertyName(), qc.getValue(), MatchMode.END));
									break;
								case BEFORE:
									criteria.add(Restrictions.like(qc.getPropertyName(), qc.getValue(), MatchMode.START));
									break;
								case ANYWHERE:
									criteria.add(Restrictions.like(qc.getPropertyName(), qc.getValue(), MatchMode.ANYWHERE));
									break;
								default:
									criteria.add(Restrictions.like(qc.getPropertyName(), qc.getValue(), MatchMode.ANYWHERE));
									break;
							}
						}
						break;
					case LT:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.lt(qc.getPropertyName(), value));
						}
						break;
					case GT:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.gt(qc.getPropertyName(), value));
						}
						break;
					case LE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.le(qc.getPropertyName(), value));
						}
						break;
					case GE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.ge(qc.getPropertyName(), value));
						}
						break;
					case NE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.ne(qc.getPropertyName(), value));
						}
						break;
					case IN:
						if (qc.getMultipleValue() != null && qc.getMultipleValue().length > 0) {
							Object[] values = new Object[qc.getMultipleValue().length];
							for (int i = 0; i < qc.getMultipleValue().length; i++) {
								values[i] = stringToObject(qc.getMultipleValue()[i], type);
							}
							criteria.add(Restrictions.in(qc.getPropertyName(), values));
						}
						break;
					case BETWEEN:
						if (StringUtils.isNotEmpty(qc.getValue()) || StringUtils.isNotEmpty(qc.getValue2())) {
							criteria.add(Restrictions.between(qc.getPropertyName(), qc.getValue(), qc.getValue2()));
						}
						break;
					default:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							criteria.add(Restrictions.eq(qc.getPropertyName(), value));
						}
						break;
				}
			}
			return criteria;
		}
		return null;
	}

	/**
	 * 根据查询条件组件 QueryBuilder
	 * QueryBuilder 的用法请参考hibernate源代码
	 *
	 * @param conditions 查询条件
	 * @return
	 */
	protected QueryBuilder buildQuery(final List<QueryCondition> conditions) {
		if (conditions != null && conditions.size() > 0) {
			String entityName = entityClass.getSimpleName();
			QueryBuilder queryBuilder = new QueryBuilder(entityName, StringUtils.uncapitalize(entityName));
			ClassMetadata metadata = getSessionFactory().getClassMetadata(entityClass);
			Parameters parameters = queryBuilder.getRootParameters();
			for (QueryCondition qc : conditions) {
				if (qc == null) continue;
				Type type = metadata.getPropertyType(qc.getPropertyName());
				switch (qc.getOperator()) {
					case LIKE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							String value = qc.getValue();
							switch (qc.getMatchMode()) {
								case EXACT:
									break;
								case BEFORE:
									value = "%" + value;
									break;
								case AFTER:
									value = value + "%";
									break;
								case ANYWHERE:
									value = "%" + value + "%";
									break;
								default:
									value = "%" + value + "%";
									break;
							}
							parameters.addWhereWithParam(qc.getPropertyName(), "like", value);
						}
						break;
					case LT:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), "<", value);
						}
						break;
					case GT:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), ">", value);
						}
						break;
					case LE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), "<=", value);
						}
						break;
					case GE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), ">=", value);
						}
						break;
					case NE:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), "!=", value);
						}
						break;
					case IN:
						if (qc.getMultipleValue() != null && qc.getMultipleValue().length > 0) {
							Object[] values = new Object[qc.getMultipleValue().length];
							for (int i = 0; i < qc.getMultipleValue().length; i++) {
								values[i] = stringToObject(qc.getMultipleValue()[i], type);
							}
							parameters.addWhereWithParams(qc.getPropertyName(), "in (", values, ")");
						}
						break;
					case BETWEEN:
						if (StringUtils.isNotEmpty(qc.getValue()) && StringUtils.isEmpty(qc.getValue2())) {
							Object minValue = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), ">=", minValue);
						} else if (StringUtils.isEmpty(qc.getValue()) && StringUtils.isNotEmpty(qc.getValue2())) {
							Object maxValue = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), "<=", maxValue);
						} else if (StringUtils.isNotEmpty(qc.getValue()) && StringUtils.isNotEmpty(qc.getValue2())) {
							Object minValue = stringToObject(qc.getValue(), type);
							Object maxValue = stringToObject(qc.getValue2(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), ">=", minValue);
							parameters.addWhereWithParam(qc.getPropertyName(), "<=", maxValue);
						}
						break;
					default:
						if (StringUtils.isNotEmpty(qc.getValue())) {
							Object value = stringToObject(qc.getValue(), type);
							parameters.addWhereWithParam(qc.getPropertyName(), "=", value);
						}
						break;
				}
			}
			return queryBuilder;
		}
		return null;
	}


	/**
	 * TODO 调用存储过程
	 *
	 * @param procedureName 存储过程的名称
	 * @param args          存储过程的参数
	 * @return 返回结果
	 */
	public final <X> List<X> callProcedure(final String procedureName,
	                                       final Object... args) {
		StringBuilder sql = new StringBuilder();
		sql.append("{Call ");
		sql.append(procedureName);
		if (args != null && args.length > 0) {
			sql.append("(");
			for (int i = 0; i < args.length; i++) {
				if (i > 0) {
					sql.append(",");
				}
				sql.append("?");
			}
			sql.append(")");
		}
		sql.append("}");
		Query query = getSession().createSQLQuery(sql.toString());
		if (args != null && args.length > 0) {
			for (int i = 0; i < args.length; i++) {
				query.setParameter(i, args[i]);
			}
		}
		return query.list();

//		Query query = getSession().createSQLQuery("{Call process_analog(?,?)}");
//		query.setParameter(0, cal);
//		query.setParameter(1, uuid);
//
//		query.executeUpdate(); //没有返回值
//query.list();//有返回值
	}

	/**
	 * 子类可以通过重载此方法添加自定义的约束条件
	 *
	 * @param criteria
	 */
	protected void addCustomCriterion(Criteria criteria, Object... parameters) {
	}

	/**
	 * 从字符串到java对象的转换
	 *
	 * @param value 值
	 * @param type  类型
	 * @return
	 */
	protected Object stringToObject(String value, Type type) {
		Object ret = null;
		if (type instanceof TimestampType) {
			SimpleDateFormat sdf = new SimpleDateFormat();
			if (value.length() > 10) {
				sdf.applyPattern("yyyy-MM-dd HH:mm:ss");
			} else {
				sdf.applyPattern("yyyy-MM-dd");
			}
			try {
				ret = sdf.parse(value);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		} else if (type instanceof AbstractStandardBasicType) {
			ret = ((AbstractStandardBasicType) type).fromString(value);
		}
		return ret;
	}

	//==================通用方法====================//

	/**
	 * 根据Cretrial查询结果
	 *
	 * @param criteria 查询条件
	 * @return
	 */
	public <X> List<X> find(final Criteria criteria) {
		return criteria.list();
	}

	/**
	 * 根据Cretrial查询结果
	 *
	 * @param criteria 查询条件
	 * @return
	 */
	public <X> List<X> find(Selector selector, final Criteria criteria) {
		List<X> poList = find(criteria);
		if (poList != null && poList.size() > 0) {
			List<X> voList = new ArrayList<X>();
			for (X po : poList) {
				X vo = toVO(po, selector);
				voList.add(vo);
			}
			return voList;
		}
		return null;
	}

	/**
	 * 按HQL查询对象列表
	 *
	 * @param <X>
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> List<X> find(final String hql, final Object... values) {
		return createQuery(hql, values).list();
	}

	/**
	 * 根据选择器和hql语句执行查询结果，返回后的结果为VO
	 *
	 * @param selector 属性选择器
	 * @param hql      hql语句
	 * @param values   参数
	 * @param <X>      X 返回对象泛型类型
	 * @return VoList
	 */
	@SuppressWarnings("unchecked")
	public <X> List<X> find(final Selector selector, final String hql,
	                        final Object... values) {
		Query query = createQuery(hql, values);
		List<X> poList = query.list();
		if (poList == null)
			return null;
		List<X> voList = new ArrayList<X>();
		for (X po : poList) {
			X vo = this.toVO(po, selector);
			voList.add(vo);
		}
		return voList;
	}

	/**
	 * 按HQL查询对象列表
	 *
	 * @param <X>
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> List<X> find(final String hql, final Map<String, Object> values) {
		return createQuery(hql, values).list();
	}

	/**
	 * 按HQL查询对象列表，并且筛选属性
	 *
	 * @param selector  选择器
	 * @param hql       查询语句
	 * @param paramters 查询参数
	 * @param <X>       返回结果泛型
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public <X> List<X> find(final Selector selector, final String hql,
	                        final Map<String, Object> paramters) {
		Query query = createQuery(hql, paramters);
		List<X> poList = query.list();
		if (poList == null)
			return null;
		List<X> voList = new ArrayList<X>();
		for (X po : poList) {
			X vo = toVO(po, selector);
			voList.add(vo);
		}
		return voList;
	}

	/**
	 * 按HQL查询唯一对象.
	 *
	 * @param <X>
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> X findUnique(final String hql, final Object... values) {
		return (X) createQuery(hql, values).uniqueResult();
	}

	/**
	 * 按HQL查询唯一对象，并且按照选择器筛选
	 *
	 * @param selector
	 * @param hql
	 * @param values
	 * @param <X>
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> X findUnique(final Selector selector, final String hql,
	                        final Object... values) {
		Query query = createQuery(hql, values);
		X po = (X) query.uniqueResult();
		if (po == null)
			return null;
		return toVO(po, selector);
	}

	/**
	 * 按HQL查询唯一对象.
	 *
	 * @param <X>
	 * @param hql    查询语句
	 * @param values 查询参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> X findUnique(final String hql, final Map<String, Object> values) {
		return (X) createQuery(hql, values).uniqueResult();
	}

	/**
	 * 按HQL查询唯一对象.
	 *
	 * @param selector
	 * @param hql
	 * @param values
	 * @param <X>
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> X findUnique(final Selector selector, final String hql,
	                        final Map<String, Object> values) {
		Query query = createQuery(hql, values);
		X po = (X) query.uniqueResult();
		if (po == null)
			return null;
		return toVO(po, selector);
	}

	/**
	 * 执行HQL进行批量修改/删除操作.
	 *
	 * @param hql    sql语句
	 * @param values 查询参数
	 * @return 执行结果集
	 */
	public int batchExecute(final String hql, final Object... values) {
		return createQuery(hql, values).executeUpdate();
	}

	/**
	 * 执行HQL进行批量修改/删除操作.
	 *
	 * @param hql    sql语句
	 * @param values 查询参数
	 * @return 执行结果集
	 */
	public int batchExecute(final String hql, final Map<String, Object> values) {
		return createQuery(hql, values).executeUpdate();
	}

	/**
	 * 根据查询HQL与参数列表创建Query对象.
	 *
	 * @param queryString sql字符串
	 * @param values      语句参数
	 * @return hibernate Query对象
	 */
	public Query createQuery(final String queryString, final Object... values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createQuery(queryString);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		return query;
	}

	/**
	 * 根据查询HQL与参数列表创建Query对象.
	 *
	 * @param queryString sql字符串
	 * @param values      语句参数
	 * @return hibernate Query对象
	 */
	public Query createQuery(final String queryString,
	                         final Map<String, Object> values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createQuery(queryString);
		if (values != null) {
			query.setProperties(values);
		}
		return query;
	}

	/**
	 * 为Query添加distinct transformer.
	 *
	 * @param query
	 * @return
	 */
	public Query distinct(Query query) {
		query.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
		return query;
	}

	/**
	 * 为Criteria添加distinct transformer.
	 *
	 * @param criteria
	 * @return
	 */
	public Criteria distinct(Criteria criteria) {
		criteria
				.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
		return criteria;
	}

	/**
	 * 删除对象
	 *
	 * @param model 实体对象
	 */
	public final void delete(Object model) {
		getHibernateTemplate().delete(model);
	}

	/**
	 * 修改对象
	 *
	 * @param model 实体对象
	 */
	public final void update(Object model) {
		getHibernateTemplate().update(model);
	}

	/**
	 * 保存对象
	 *
	 * @param model 实体对象
	 */
	public final void save(Object model) {
		getHibernateTemplate().save(model);
	}

	/**
	 * 从PO到VO的转换，自动加载属性，如果有延迟加载，根据选择器加载，如果没有延迟加载，
	 *
	 * @param <X>
	 * @param object
	 * @return
	 */
	public <X> X toVO(X object) {
		return toVO(object, null);
	}

	/**
	 * 从PO到VO的转换，根据属性选择器，自动加载属性
	 *
	 * @param <X>
	 * @param object
	 * @param selector 属性选择器
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <X> X toVO(X object, Selector selector) {
		// 参数判断
		if (object == null)
			return null;
		X newObject = null;
		try {
			Class<X> destClass = HibernateProxyHelper
					.getClassWithoutInitializingProxy(object);
			//要求所有的PO都有一个无参的构造方法
			newObject = destClass.newInstance();
			ClassMetadata metadata = getSessionFactory().getClassMetadata(
					destClass);
			// 主键属性名
			String identityfier = metadata.getIdentifierPropertyName();
			// 复制主键
			ReflectionUtils.setProperty(newObject, identityfier,
					ReflectionUtils.getProperty(object, identityfier));
			// TODO 判断实体的加载模式，如果是即时加载，就跟属性一样直接加载
			// Object idVal = metadata.getPropertyValue(object, identityfier,
			// EntityMode.POJO);
			// metadata.setPropertyValue(newObject, identityfier, idVal,
			// EntityMode.POJO);
			// metadata.hasProxy();
			// boolean[] lazies = metadata.getPropertyLaziness();
			String[] properties = metadata.getPropertyNames();
			for (int i = 0; i < properties.length; i++) {
				String prop = properties[i];
				Type propType = metadata.getPropertyType(prop);

				// 简单属性，直接copy
				if (!propType.isEntityType() && !propType.isCollectionType()) {
					// 其他标量的属性，直接copy
					ReflectionUtils.setProperty(newObject, prop,
							ReflectionUtils.getProperty(object, prop));
					// metadata.setPropertyValue(newObject, prop,
					// metadata.getPropertyValue(object, prop, EntityMode.POJO),
					// EntityMode.POJO);
					continue;
				}
				// 复杂属性，先判断选择器是否为空，为空就直接跳过
				if (selector == null)
					continue;
				// 判断是否要加载此对象
				boolean needLoad = needLoad(propType, true, prop, selector);
				// 如果不要加载，直接跳过
				if (!needLoad)
					continue;
				// 取得属性值
				Object propVal = ReflectionUtils.getProperty(object, prop);
				// Object propVal = metadata.getPropertyValue(object, prop,
				// EntityMode.POJO);
				// 如果属性值为空，直接跳过
				if (propVal == null)
					continue;
				// 获取选择器的子选择器
				Selector subSelector = selector.generateSubSelector(prop);
				// 如果是实体类型，直接递归调用
				if (propType.isEntityType()) {
					ReflectionUtils.setProperty(newObject, prop, toVO(propVal,
							subSelector));
					// metadata.setPropertyValue(newObject, prop, propVal,
					// EntityMode.POJO);
					continue;
				}
				// 如果是集合属性，分各种情况处理
				if (propType.isCollectionType()) {
					// list型的属性
					if (propType instanceof ListType) {
						List listVal = (List) propVal;
						if (listVal.size() == 0)
							continue;
						List newVal = new ArrayList(listVal.size());
						for (Iterator it = listVal.iterator(); it.hasNext(); ) {
							newVal.add(toVO(it.next(), subSelector));
						}
						ReflectionUtils.setProperty(newObject, prop, newVal);
						// metadata.setPropertyValue(newObject, prop, newVal,
						// EntityMode.POJO);
						continue;
					}
					// Set型的属性
					if (propType instanceof SetType) {
						Set setVal = (Set) propVal;
						if (setVal.size() == 0)
							continue;
						Set newVal = new HashSet(setVal.size());
						for (Iterator it = setVal.iterator(); it.hasNext(); ) {
							newVal.add(toVO(it.next(), subSelector));
						}
						ReflectionUtils.setProperty(newObject, prop, newVal);
						// metadata.setPropertyValue(newObject, prop, newVal,
						// EntityMode.POJO);
						continue;
					}
					// Map型的属性
					if (propType instanceof MapType) {
						Map mapVal = (Map) propVal;
						if (mapVal.size() == 0)
							continue;
						Map newVal = new HashMap(mapVal.size());
						for (Iterator<Map.Entry> it = mapVal.entrySet().iterator(); it
								.hasNext(); ) {
							Map.Entry entry = it.next();
							newVal.put(entry.getKey(), toVO(entry.getValue(),
									subSelector));
						}
						ReflectionUtils.setProperty(newObject, prop, newVal);
						// metadata.setPropertyValue(newObject, prop, newVal,
						// EntityMode.POJO);
						continue;
					}
					// 备注 bagType和List相同处理
					if (propType instanceof BagType) {
						List listVal = (List) propVal;
						if (listVal.size() == 0)
							continue;
						List<Object> newVal = new ArrayList<Object>(listVal
								.size());
						for (Iterator it = listVal.iterator(); it.hasNext(); ) {
							newVal.add(toVO(it.next(), subSelector));
						}
						ReflectionUtils.setProperty(newObject, prop, newVal);
						// metadata.setPropertyValue(newObject, prop, newVal,
						// EntityMode.POJO);
						continue;
					}
					// 数组型的属性
					if (propType instanceof ArrayType) {
						Object[] arrVal = (Object[]) propVal;
						if (arrVal.length == 0)
							continue;
						Object[] newVal = new Object[arrVal.length];
						for (int j = 0; j < arrVal.length; j++) {
							newVal[j] = toVO(arrVal[j], subSelector);
						}
						ReflectionUtils.setProperty(newObject, prop, newVal);
						// metadata.setPropertyValue(newObject, prop, newVal,
						// EntityMode.POJO);
						continue;
					}
				}
			}

		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return newObject;
	}

	/**
	 * 判断一个对象是否要实例化
	 *
	 * @param lazy     是否为延迟加载的对象
	 * @param property 属性名
	 * @param selector 选择器
	 * @return
	 */
	protected boolean needLoad(Type propType, boolean lazy, String property,
	                           Selector selector) {
		if (!lazy)
			return true;
		if (!propType.isEntityType() && !propType.isCollectionType())
			return true;
		if (selector.containsProperty(property))
			return true;
		return false;
	}
}
